-- @owner: @zhangbingliang
-- @date: 2025-07-31
-- @testpoint: 事务内使用fetch_status函数
-- @modify: modified by @zhangbingliang at 2025-07-31

--prestep1:连接到D库
@conn PrimaryDbDmode;

--step1:建表;expect:成功
drop table if exists t_fetch0004;
CREATE TABLE t_fetch0004 (
 ID INT PRIMARY KEY,
 Value NVARCHAR(50)
);

INSERT INTO t_fetch0004 VALUES (1, 'A'), (2, 'B'), (3, 'C');

--step2:事务提交前删除数据使用函数查询;expect:最后一个fetch结果为空，函数返回-1，其余返回0
start transaction;
DECLARE cur_fetch0004_01 CURSOR FOR SELECT ID, Value FROM t_fetch0004; /
DELETE FROM t_fetch0004 WHERE ID = 2;
FETCH NEXT FROM cur_fetch0004_01;
select @@FETCH_STATUS;
FETCH NEXT FROM cur_fetch0004_01;
select @@FETCH_STATUS;
FETCH last FROM cur_fetch0004_01;
select @@FETCH_STATUS;
FETCH NEXT FROM cur_fetch0004_01;
select @@FETCH_STATUS;
CLOSE cur_fetch0004_01;
end;

--step3:插入数据
INSERT INTO t_fetch0004 VALUES (2, 'B');

--step4:删除后回滚，函数返回0
BEGIN; /
DECLARE cur_fetch0004_02 cursor FOR SELECT * FROM t_fetch0004; /
FETCH NEXT FROM cur_fetch0004_02;
select @@FETCH_STATUS; 
DELETE FROM t_fetch0004 WHERE ID = 1;
FETCH ABSOLUTE 1 FROM cur_fetch0004_02;
select @@FETCH_STATUS;
CLOSE cur_fetch0004_02;
ROLLBACK;

--清理环境
drop table if exists t_fetch0004;